# -*- coding: utf-8 -*-

'''
@author: Luís Fonte nº6043
@date 03/12/2012
@obs:
Creates graphics from statistics
'''

import xlrd
import sqlalchemy
from sqlalchemy.orm import sessionmaker
from sqlalchemy import *
import csv
import pylab as p


# Creation of all graphics based on these tutorials:
# http://matplotlib.org/examples/api/barchart_demo.html
# http://scienceoss.com/bar-plot-with-custom-axis-labels/


"""
 Generic function to create a graphic

 @param y_values - values for the yy axis on graphic
 @param color - bar color
 @param label_up - title of graphic
 @param label_left - subtitle of yy axis data
 @param valueDistance_bar - distance from bar and label with value of it
"""
def makeGraphic(y_values, color, label_up, label_left, valueDistance_bar):
    
    years_list = ['1995/96','1996/97','1997/98','1998/99','1999/00','2000/01','2001/02','2002/03','2003/04','2004/05','2005/06','2006/07','2007/08','2008/09','2009/10','2010/11']
    
    # Creates graphic form, xx and yy axis
    fig = p.figure()
    ax = fig.add_subplot(1,1,1)
    y = y_values  # query
    N = len(y)
    ind = range(N)
    # bars atributes
    rects = ax.bar(ind, y, facecolor=color,
           align='center', ecolor='black')
    ax.set_ylabel(label_left)
    ax.set_title(label_up, fontstyle='italic')
    ax.set_xticks(ind)
    # label bottom
    group_labels = years_list
    ax.set_xticklabels(group_labels)
    for rect in rects:
        height = rect.get_height()
        ax.text(rect.get_x()+rect.get_width()/2., valueDistance_bar*height, '%d'%int(height), 
                ha='center', va='bottom') # rects values to be shown on graphic
    fig.autofmt_xdate()
    p.show()
    pass

"""
 Generic query for graphic creation
 @param formation_type - type of formation to search on database
"""
def queryForGraphic(formation_type):
    
    # Connecttion do database
    engine = create_engine('sqlite:///inscritos.db', echo=False) # False cause there's no need to print information of engine creation
    metadata = MetaData(engine)
    # Definition of the table on database
    tableInscritos = Table('inscritos', metadata, autoload = True)
     
    # create a Session
    Session = sessionmaker(bind=engine)
    session = Session()
     
    
    # searches for every row that contains the words "Computadores" and "Informática" on Curso_Superior column and formation type on Nivel_de_Formacao column
    query = session.query(tableInscritos). filter(tableInscritos.c.Curso_Superior.like(u'%Computadores%'), tableInscritos.c.Curso_Superior.like(u'%Informática%'), tableInscritos.c.Nivel_de_Formacao.like(u'L - Licenciatura')) # c of column
    
    listFromQuery = fillList(query) # creates a list with query data given, to be the yy axis values of the graphic

    return listFromQuery

    pass


    
"""
 Graphic 1
 Average values with number of students registered over the years
"""
def makeGraph1():

    makeGraphic(queryForGraph1(), '#FFFF00', 'Media do numero de alunos dos cursos com o termo Computadores e Informatica, ao longo dos anos' + '\n',
                'Numero de Alunos (Media)', 1.0090)
    pass

"""
 Query that obtains data to fill the yy axis on graphic
"""
def queryForGraph1():

    # Connecttion do database
    engine = create_engine('sqlite:///inscritos.db', echo=False) # False cause there's no need to print information of engine creation
    metadata = MetaData(engine)
    # Definition of the table on database
    tabelaInscritos = Table('inscritos', metadata, autoload = True)
     
    # create a Session
    Session = sessionmaker(bind=engine)
    session = Session()
     
    
    # searches for every row that contains the words "Computadores" and "Informática" on Curso_Superior column
    query = session.query(tabelaInscritos). filter(tabelaInscritos.c.Curso_Superior.like(u'%Computadores%'), tabelaInscritos.c.Curso_Superior.like(u'%Informática%')) # c of column
    
    listFromQuery = fillList_Graph1_Average(query) # creates a list with query data given, to be the yy axis values of the graphic

    return listFromQuery

    pass

"""
 Graphic 2 - for 1st formation
 L - Licenciatura
"""
def makeGraphicFormations1():

    makeGraphic(queryForGraphic(u'L - Licenciatura'), '#C22DD2', 'Numero de alunos dos cursos com o termo Computadores e Informatica, por Tipo de Formacao' + '\n' + 'L - Licenciatura',
                'Numero de Alunos', 1.02)
    pass
    

"""
 Graphic 2 - for 2nd formation
 LB - Bacharelato+Licenciatura
"""

def makeGraphicFormations2():

    makeGraphic(queryForGraphic(u'LB - Bacharelato+Licenciatura'), '#FB003F', 'Numero de alunos dos cursos com o termo Computadores e Informatica, por Tipo de Formacao' + '\n' + 'LB - Bacharelato+Licenciatura',
                'Numero de Alunos', 1.01)
    pass


"""
 Graphic 2 - for 3rd formation
 P - Preparatórios de licenciatura
"""
def makeGraphicFormations3():

    makeGraphic(queryForGraphic(u'P - Preparatórios de licenciatura'), '#7CFC00', 'Numero de alunos dos cursos com o termo Computadores e Informatica, por Tipo de Formacao' + '\n' + 'P - Prepatarorios de licenciatura',
                'Numero de Alunos', 1.029)

    pass



"""
 Graphic 2 - for 4th formation
 L1 - Licenciatura - 1º ciclo
"""
def makeGraphicFormations4():

    makeGraphic(queryForGraphic(u'L1 - Licenciatura - 1º ciclo'), '#2DCDD2', 'Numero de alunos dos cursos com o termo Computadores e Informatica, por Tipo de Formacao' + '\n' + 'L1 - Licenciatura - 1o ciclo',
                'Numero de Alunos', 1.029)

    pass


"""
 Graphic 2 - for 5th formation
 M2 - Mestrado - 2º ciclo
"""
def makeGraphicFormations5():

    makeGraphic(queryForGraphic(u'M2 - Mestrado - 2º ciclo'), '#18EF23', 'Numero de alunos dos cursos com o termo Computadores e Informatica, por Tipo de Formacao' + '\n' + 'M2 - Mestrado - 2o ciclo',
                'Numero de Alunos', 1.029)
    pass




"""
  Graphic 2 - for 6th formation
  D - Doutoramento
"""
def makeGraphicFormations6():

    makeGraphic(queryForGraphic(u'D - Doutoramento'), '#FB8A00', 'Numero de alunos dos cursos com o termo Computadores e Informatica, por Tipo de Formacao' + '\n' + 'D - Doutoramento',
                'Numero de Alunos', 1.029)
    pass



"""
  Graphic 2 - for 7th formation
  D3 - Doutoramento - 3º ciclo
"""
def makeGraphicFormations7():

    makeGraphic(queryForGraphic(u'D3 - Doutoramento - 3º ciclo'), '#5800FB', 'Numero de alunos dos cursos com o termo Computadores e Informatica, por Tipo de Formacao' + '\n' + 'D3 - Doutoramento - 3o ciclo',
                'Numero de Alunos', 1.01)
    pass


    

"""
 Graphic 2 - for 8th formation
 PL - Preparatório de licenciatura 1ºciclo
"""
def makeGraphicFormations8():

    makeGraphic(queryForGraphic(u'PL - Preparatório de licenciatura 1ºciclo'), '#C00000', 'Numero de alunos dos cursos com o termo Computadores e Informatica, por Tipo de Formacao' + '\n' + 'PL - Prepatarorio de licenciatura 1o ciclo',
                'Numero de Alunos (Media)', 1.0)

    pass


"""
 Method used for the function for Graphic 1 to fill list with data from years, from all classes
 @param query - specific query for that graphic
"""
def fillList_Graph1_Average(query):

    listFinal = []      # list with number of students per year - Integer List
    
    for row in query:
        listTmp = []
        for r in range(6,22):
            if row[r] != '-':   # If the cell value is different of '-', then there are students registered on that curse, that year. So increments number of students on that year, on that formation obtained from query
                listTmp.append(float(row[r]))
            else:
                listTmp.append(int(0))  # If its equal to '-', there is a closed class that year and there are no students on that class. So appends 0 to list
            pass
        
        if len(listFinal) != len(listTmp):
            listFinal = listTmp
        else:
            for i in range(len(listTmp)):   # creates the list with the final values of students on all formations passed through query given, per year
                listFinal[i] += listTmp[i]
                pass
        pass

    for t in range(len(listFinal)):
        listFinal[t] = listFinal[t] / 16   #divides per 16 to get the average

    return listFinal


"""
 Method used for all functions to fill list with data from years
 @param query - specific query for that graphic
"""
def fillList(query):

    listFinal = []      # list with number of students per year - Integer List
    
    for row in query:
        listTmp = []
        for r in range(6,22):
            if row[r] != '-':   # If the cell value is different of '-', then there are students registered on that curse, that year. So increments number of students on that year, on that formation obtained from query
                listTmp.append(float(row[r]))
            else:
                listTmp.append(int(0))  # If its equal to '-', there is a closed class that year and there are no students on that class. So appends 0 to list
            pass
        
        if len(listFinal) != len(listTmp):
            listFinal = listTmp
        else:
            for i in range(len(listTmp)):    # creates the list with the final values of students on that formation passed through query given, per year
                listFinal[i] += listTmp[i]
                pass
        pass


    return listFinal



